package com.zyd.poi.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@RestController
@Api(tags = "EXCEL接口")
public class ExcelController
{
    @ApiOperation(value = "xls")
    @GetMapping("/xls")
    public void xls()
    {
        Resource resource = new ClassPathResource("data/check.xls");
        try (HSSFWorkbook workbook = new HSSFWorkbook(resource.getInputStream()))
        {
            for (int index = 0; index < workbook.getNumberOfSheets(); index++)
            {
                HSSFSheet sheet = workbook.getSheetAt(index);
                for (int rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++)
                {
                    HSSFRow row = sheet.getRow(rowIndex);
                    log.info("{}-{}-0 ==> {}", index, rowIndex, readCell(row, 0));
                    if (rowIndex > 10)
                    {
                        break;
                    }
                }
            }
            
            // 修改
            HSSFSheet sheet = workbook.getSheetAt(0);
            HSSFRow row = sheet.getRow(2);
            row.createCell(5).setCellValue("00fly,");
            row.createCell(6).setCellValue("hello");
            row.createCell(7).setCellValue("world!");
            
            // 保存新文件
            String fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMdd-HHmmss") + ".xls";
            File rootDir = new File("upload");
            rootDir.mkdirs();
            try (OutputStream outputstream = new FileOutputStream(rootDir.getCanonicalPath() + "/" + fileName))
            {
                workbook.write(outputstream);
                Runtime.getRuntime().exec("cmd /c start " + rootDir);
            }
        }
        catch (IOException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    @ApiOperation(value = "xlsx")
    @GetMapping("/xlsx")
    public void simple()
    {
        Resource resource = new ClassPathResource("data/xxx表设计_00fly.xlsx");
        try (XSSFWorkbook workbook = new XSSFWorkbook(resource.getInputStream()))
        {
            for (int index = 0; index < workbook.getNumberOfSheets(); index++)
            {
                XSSFSheet sheet = workbook.getSheetAt(index);
                for (int rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++)
                {
                    XSSFRow row = sheet.getRow(rowIndex);
                    log.info("{}-{}-0 ==> {}", index, rowIndex, readCell(row, 0));
                    if (rowIndex > 10)
                    {
                        break;
                    }
                }
            }
            
            // 修改,注意getCell会保留原来的格式
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row = sheet.getRow(2);
            row.getCell(5).setCellValue("00fly,");
            // row.createCell(5).setCellValue("00fly,");
            row.createCell(6).setCellValue("hello");
            row.createCell(7).setCellValue("world!");
            
            // 保存新文件
            String fileName = DateFormatUtils.format(System.currentTimeMillis(), "yyyyMMdd-HHmmss") + ".xlsx";
            File rootDir = new File("upload");
            rootDir.mkdirs();
            try (OutputStream outputstream = new FileOutputStream(rootDir.getCanonicalPath() + "/" + fileName))
            {
                workbook.write(outputstream);
                Runtime.getRuntime().exec("cmd /c start " + rootDir);
            }
        }
        catch (IOException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * 读取row行第index列表格文本内容
     * 
     * @param row
     * @param index
     * @return
     * @see [类、类#方法、类#成员]
     */
    private String readCell(HSSFRow row, int index)
    {
        HSSFCell cell = row.getCell(index);
        if (cell != null)
        {
            cell.setCellType(CellType.STRING);
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        }
        return "";
    }
    
    /**
     * 读取row行第index列表格文本内容
     * 
     * @param row
     * @param index
     * @return
     * @see [类、类#方法、类#成员]
     */
    private String readCell(XSSFRow row, int index)
    {
        XSSFCell cell = row.getCell(index);
        if (cell != null)
        {
            cell.setCellType(CellType.STRING);
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        }
        return "";
    }
}
